/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package merlion.mrp.ppm.session;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Iterator;
import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import merlion.crm.slcm.session.SLCSessionBeanLocal;
import merlion.mrp.ppm.entity.DailyMPS;
import merlion.mrp.ppm.entity.DailyPlannedProduction;
import merlion.mrp.ppm.entity.Product;
import merlion.mrp.ppm.entity.SOP;
import merlion.mrp.ppm.entity.SalesForecast;

/**
 *
 * @author Zhang Ying, Zhu Xin
 * 
 * getSessionSalesForecastData
getInventoryOfPrevSeasonLastMonth
getWorkingDaysList
 * checkAbleToCreateSop
 */
@Stateless
public class SalesOptPlanningSession implements SalesOptPlanningSessionLocal, SalesOptPlanningSessionRemote {

    @PersistenceContext
    private EntityManager entityManager;
    @EJB
    private MPSPlanningSessionLocal mpsSession;
    @EJB
    private SLCSessionBeanLocal slcSession;

    @Override
    public ArrayList<ArrayList<Long>> getSeasonSalesForecastData(int sopStartYear, int sopStartMonth) {
        // return size: 4*3;
        // ArrayList<Long> contains 3 months' sales forecast's lastest final values of one product, the lastest version is obtained from the last UpdateIndiv of one salesforecast
        // the outer arraylist:  0->product A, 1->B, 2->C, 3->D
        ArrayList<ArrayList<Long>> sfData = new ArrayList<ArrayList<Long>>();  //the data to be returned

        SalesForecast sf = getSalesForecast(sopStartYear, sopStartMonth);
        if (sf == null) {
            return null;
        }

        ArrayList<Long> indivSF = new ArrayList<Long>();
        Integer m = new Integer(sopStartMonth);

        //Zhang Ying must see this: actually the lastest final values is not from UpdateIndiv.
        //By the time UpdateIndiv is done, the SOP should be already created.
        if (sf.getStartPeriodMonth().equals(m)) {  //get the first three months' data in sales forecast(first quarter data requested)
            for (int i = 0; i < 4; i++) {
                for (int j = 0; j < 3; j++) {  //get the forecasted value for individual product
                    indivSF.add(sf.returnProductIndivForecast(i).getFinalValues().get(j).getLongValue());
                }
                sfData.add(indivSF);
                indivSF = new ArrayList<Long>();
            }
        } else { //get the last three months' data in sales forecast
            for (int i = 0; i < 4; i++) {
                for (int j = 3; j < 6; j++) { //get the forecasted value for individual product
                    indivSF.add(sf.returnProductIndivForecast(i).getFinalValues().get(j).getLongValue());
                }
                sfData.add(indivSF);
                indivSF = new ArrayList<Long>();
            }
        }

        return sfData;
    }

    @Override
    public ArrayList<ArrayList<Long>> getSeasonSalesForecastDataForUpdate(int sopStartYear, int sopStartMonth) {
        ArrayList<ArrayList<Long>> sfData = new ArrayList<ArrayList<Long>>();  //the data to be returned

        SalesForecast sf = getSalesForecast(sopStartYear, sopStartMonth);

        ArrayList<Long> indivSF = new ArrayList<Long>();
        Integer m = new Integer(sopStartMonth);

        if (sf.getStartPeriodMonth().equals(m)) {  //get the first three months' data in sales forecast(first quarter data requested)
            for (int i = 0; i < 4; i++) {
                int times = sf.returnProductIndivForecast(i).returnNumberOfUpdates();  //number of updates
                for (int j = 0; j < 3; j++) {  //get the forecasted value for individual product
                    indivSF.add(sf.returnProductIndivForecast(i).returnUpdateIndivs(times - 1).getFinalValues().get(j).getLongValue());
                }
                sfData.add(indivSF);
                indivSF = new ArrayList<Long>();
            }
        } else { //get the last three months' data in sales forecast
            for (int i = 0; i < 4; i++) {
                int times = sf.returnProductIndivForecast(i).returnNumberOfUpdates(); //number of updates
                for (int j = 3; j < 6; j++) { //get the forecasted value for individual product
                    indivSF.add(sf.returnProductIndivForecast(i).returnUpdateIndivs(times - 1).getFinalValues().get(j).getLongValue());
                }
                sfData.add(indivSF);
                indivSF = new ArrayList<Long>();
            }
        }

        return sfData;
    }

    @Override
    public SalesForecast getSalesForecast(int sopStartYear, int sopStartMonth) {

        Integer y = new Integer(sopStartYear);
        Integer m;
        if (sopStartMonth == 1 || sopStartMonth == 4) {
            m = new Integer(1);
        } else {
            m = new Integer(7);
        }
        try {
            Query query = entityManager.createQuery("SELECT sf FROM SalesForecast sf WHERE sf.startPeriodYear=:p1 AND sf.startPeriodMonth=:p2");
            query.setParameter("p1", y);
            query.setParameter("p2", m);
            if (query.getResultList().isEmpty()) {
                return null;
            }
            return (SalesForecast) query.getResultList().get(0);
        } catch (Exception ex) {
            return null;
        }
    }

    //For update
    @Override
    public boolean persistMrpSop(SOP sop) {
        try {
            entityManager.merge(sop);
            // UPDATED: update all the w mps

            mpsSession.updateWeeklyMPS(sop.getStartPeriodYear(), sop.getStartPeriodMonth());
            mpsSession.updateWeeklyMPS(sop.getStartPeriodYear(), sop.getStartPeriodMonth() + 1);
            mpsSession.updateWeeklyMPS(sop.getStartPeriodYear(), sop.getStartPeriodMonth() + 2);
         

            int usedYear = 0, usedMonth = 0;
            if (sop.getStartPeriodMonth() < 2) {
                usedYear = sop.getStartPeriodYear() - 1;
                usedMonth = 12;
              
            } else {
                usedYear = sop.getStartPeriodYear();
                usedMonth = sop.getStartPeriodMonth() - 1;
               
            }
            mpsSession.updateWeeklyMPS(usedYear, usedMonth);

            //update all daily mps  
            for (int i = 0; i < 3; i++) {
                int count = mpsSession.getNumberOfWeeks(sop.getStartPeriodYear(), sop.getStartPeriodMonth());
                for (int j = 1; j <= count; j++) {
                    mpsSession.updateDailyMPS(sop.getStartPeriodYear(), sop.getStartPeriodMonth() + i, j);
                   
                }
            }
         
            //update the daily MPS for the last week of the previous SOP
            int lastYear;
            int lastMonth;
            int lastWeek;
            if (sop.getStartPeriodMonth() == 1) {
                lastYear = sop.getStartPeriodYear() - 1;
                lastMonth = 12;
                  
            } else {
                lastYear = sop.getStartPeriodYear();
                lastMonth = sop.getStartPeriodMonth() - 1;
              
            }
            lastWeek = mpsSession.getNumberOfWeeks(lastYear, lastMonth);
     
            mpsSession.updateDailyMPS(lastYear, lastMonth, lastWeek);
          


            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }

    @Override
    public ArrayList<Long> getInventoryOfPrevSeasonLastMonth(int sopStartYear, int sopStartMonth) {
        // return an arraylist of size 4.
        // 0 -> product a's inventory from the latest version of previous season sop, 1-> b, 2->c, 3->d

        ArrayList<Long> inventoryList = new ArrayList<Long>();
        SOP sop = getSOP(sopStartYear, sopStartMonth - 3); //get the previous sop
        if (sop == null) {
            for (int i = 0; i < 4; i++) {
                inventoryList.add(new Long(0));
            }
            return inventoryList;
        }
        for (int i = 0; i < 4; i++) {
            inventoryList.add(sop.returnProductIndivSOP(i).getInventoryList().get(2).getLongValue());
        }
        return inventoryList;
    }

    //This method search for the sop based on the SOP start year and start month
    @Override
    public SOP getSOP(int sopStartYear, int sopStartMonth) {
        Integer y = new Integer(sopStartYear);
        Integer m = new Integer(sopStartMonth);

        try {
            Query query = entityManager.createQuery("SELECT sop FROM SOP sop WHERE sop.startPeriodYear=:p1 AND sop.startPeriodMonth=:p2");
            query.setParameter("p1", y);
            query.setParameter("p2", m);
            if (query.getResultList().isEmpty()) {
                return null;
            }
            return (SOP) query.getResultList().get(0);
        } catch (Exception ex) {
            return null;
        }
    }

    @Override
    public SOP getMrpSop(String sopIdStr) {
        if (sopIdStr == null || sopIdStr.equals("")) {
            return null;
        }
        Query query = entityManager.createQuery("SELECT s FROM SOP s WHERE s.sopIdStr=:p1");
        query.setParameter("p1", sopIdStr);
        return (SOP) query.getSingleResult();
    }

    @Override
    public ArrayList<Integer> getWorkingDaysList(int sopStartYear, int sopStartMonth) {
        ArrayList<Integer> WDList = new ArrayList<Integer>(3);
        WDList.add(numberOfWorkingDays(sopStartYear, sopStartMonth));
        WDList.add(numberOfWorkingDays(sopStartYear, sopStartMonth + 1));
        WDList.add(numberOfWorkingDays(sopStartYear, sopStartMonth + 2));
        return WDList;
    }

    //updated for Calendar
    private Integer numberOfWorkingDays(int year, int month) {

        Integer numberOfWDs = 0;
        Calendar startCal;

        startCal = Calendar.getInstance();
        startCal.set(year, month - 1, 1,0,0,0); //start day of the month
        int maxDay = startCal.getActualMaximum(Calendar.DAY_OF_MONTH);  //number of days in the month
        for (int i = 0; i < maxDay; i++) {
            if (!isPublicHoliday(startCal) && startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY && startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
                numberOfWDs++;
            }
            startCal.add(Calendar.DAY_OF_MONTH, 1);
        }
        return numberOfWDs;
    }

    //This method determines whether the given day is a public holiday
    @Override
    public boolean isPublicHoliday(Calendar cal) {
        boolean isPH = false;
        Timestamp tsp = new Timestamp(cal.getTimeInMillis());
        Query query = entityManager.createQuery("SELECT ph FROM PublicHoliday ph WHERE ph.dateOfHoliday =:p1");
        query.setParameter("p1", tsp);
        if (!query.getResultList().isEmpty()) {
            isPH = true;
        }
        return isPH;
    }

    @Override
    public boolean checkAbleToCreateSop(int sopStartYear, int sopStartMonth, Timestamp currentTime) {
        boolean valid = true;

        /*check whether the current time is within the freezing period, if yes, cannot creat SOP
        Calendar cal = Calendar.getInstance();
        cal.set(sopStartYear, sopStartMonth, 1);   //start date of SOP
        
        long daysBetween = getDaysBetween(cal.getTimeInMillis(),currentTime.getTime());
        if(daysBetween < 21) {
        valid = false;
        }*/

        //Check whether there is existing SOP
        SOP existing = getSOP(sopStartYear, sopStartMonth);
        if (existing != null) {
            return false;
        }


        //check whether the corresponding sales forecast exist. If not, the SOP cannot be created
        SalesForecast sf = getSalesForecast(sopStartYear, sopStartMonth);
        if (sf == null) {
            return false;
        }

        //check whether previous SOP exist, if yes, the user must create SOP for the previous period first
        if (!getAllMrpSops().isEmpty() && !checkCreatedSOPBefore(sopStartYear, sopStartMonth)) {
            return false;
        }

        if (getAllMrpSops().isEmpty()) {  //the first SOP
            // sopStartMonth 1 4 7 10
            SimpleDateFormat sdf = new SimpleDateFormat("MM");
            int currentMonth = Integer.valueOf(sdf.format(currentTime));
            sdf = new SimpleDateFormat("yyyy");
            int currentYear = Integer.valueOf(sdf.format(currentTime));
            if (currentYear == sopStartYear) {
                if (sopStartMonth == 4 & currentMonth < 4) {
                    return true;
                } else if (sopStartMonth == 7 & currentMonth < 7 && currentMonth >= 4) {
                    return true;
                } else if (sopStartMonth == 10 & currentMonth < 10 && currentMonth >= 7) {
                    return true;
                } else {
                    return false;
                }
            } else if (currentYear == sopStartYear - 1) {
                if (sopStartMonth == 1 && currentMonth >= 10) {
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        }

        return valid;
    }

    private long getDaysBetween(Long end, Long start) {
        return (end - start) / (1000 * 60 * 60 * 24);
    }

    @Override
    public Collection<SOP> getAllMrpSops() {
        Query query = entityManager.createQuery("SELECT s FROM SOP s");
        return query.getResultList();
    }

    @Override
    public boolean checkCreatedSOPBefore(int sopStartYear, int sopStartMonth) {
        int year, month;
        if (sopStartMonth == 1) {
            year = sopStartYear - 1;
            month = 10;
        } else {
            year = sopStartYear;
            month = sopStartMonth - 3;
        }
        SOP sop = getSOP(year, month);
        if (sop == null) {
            return false;
        } else {
            return true;
        }
    }

    //for create
    @Override
    public boolean saveMrpSop(SOP newSop) {
        try {
            SalesForecast sf = getSalesForecast(newSop.getStartPeriodYear(), newSop.getStartPeriodMonth());
            sf.getSops().add(newSop);
            //entityManager.merge(newSop);
            entityManager.merge(sf);
            entityManager.flush();
            Query query = entityManager.createQuery("SELECT s FROM SOP s WHERE s.sopIdStr=:p1");
            query.setParameter("p1", newSop.getSopIdStr());
            SOP s = (SOP) query.getSingleResult();
            //entityManager.merge(s);
            // UPDATED: create all w mps, remember to merge
            mpsSession.createWeeklyMPS(s.getStartPeriodYear(), s.getStartPeriodMonth());
            mpsSession.createWeeklyMPS(s.getStartPeriodYear(), s.getStartPeriodMonth() + 1);
            mpsSession.createWeeklyMPS(s.getStartPeriodYear(), s.getStartPeriodMonth() + 2);

            int usedYear = 0, usedMonth = 0;
            if (s.getStartPeriodMonth() < 2) {
                usedYear = s.getStartPeriodYear() - 1;
                usedMonth = 12;
            } else {
                usedYear = s.getStartPeriodYear();
                usedMonth = s.getStartPeriodMonth() - 1;
            }


            mpsSession.updateWeeklyMPS(usedYear, usedMonth);  //the sop may affect the weekly MPS of last month


            //create all daily mps
            for (int i = 0; i < 3; i++) {
                int count = mpsSession.getNumberOfWeeks(s.getStartPeriodYear(), s.getStartPeriodMonth() + i);
                for (int j = 1; j <= count; j++) {
                    mpsSession.createDailyMPS(s.getStartPeriodYear(), s.getStartPeriodMonth() + i, j);
                    System.out.println("sop create count : " + count + "for month" + i);
                }
            }

            //update the daily MPS for the last week of the previous SOP
            int lastYear;
            int lastMonth;
            int lastWeek;
            if (s.getStartPeriodMonth() == 1) {
                lastYear = s.getStartPeriodYear() - 1;
                lastMonth = 12;
            } else {
                lastYear = s.getStartPeriodYear();
                lastMonth = s.getStartPeriodMonth() - 1;
            }
            lastWeek = mpsSession.getNumberOfWeeks(lastYear, lastMonth);
            mpsSession.updateDailyMPS(lastYear, lastMonth, lastWeek);

            //updated on 11/05/2011 
            for (int i = 0; i < 4; i++) {
                String productType = "";
                if (i == 0) {
                    productType = "Classic Nuts";
                } else if (i == 1) {
                    productType = "Classic Fruits";
                } else if (i == 2) {
                    productType = "Premium Chocolate Nuts";
                } else {
                    productType = "Premium Redcurrant Fruits";
                }
                Product p = slcSession.getProductByProductType(productType);

                //update all DailyMPS
                for (int j = 0; j < 3; j++) {  //for three month
                    ArrayList<DailyPlannedProduction> dpps = getMonthlyDailyPlannedProductionByProduct(p, s.getStartPeriodYear(), s.getStartPeriodMonth() + j);
//                    System.out.println("length laha:"  + dpps.size());
//                    System.out.println("#################sopSession line 436" +dpps.get(dpps.size()-1).getProductionDayL()/86400000);
                    Long totalAmount = 0l;
                    for (DailyPlannedProduction dpp : dpps) {
                        totalAmount += dpp.getDailyDemand();
                    }
                    Long difference = totalAmount - s.returnProductIndivSOP(i).getProductionPlanList().get(j).getLongValue();
                    if (difference > 0) { //increased
                        for (int k = 0; k < difference; k++) {
                            dpps.get(k).setDailyDemand(dpps.get(k).getDailyDemand() - 1);
                            entityManager.merge(dpps.get(k));
                            entityManager.flush();
                        }
                    } else if (difference < 0) { //decreased
                        for (int k = 0; k < (0-difference); k++) {
                            dpps.get(k).setDailyDemand(dpps.get(k).getDailyDemand() + 1);
                            entityManager.merge(dpps.get(k));
                            entityManager.flush();
                        }
                    }
                }
            }

            //get all affected dailyMPS
            Query query1 = entityManager.createQuery("SELECT dmps FROM DailyMPS dmps where dmps.startPeriodYear =:p1 AND dmps.startPeriodMonth >=:p2 AND dmps.startPeriodMonth <=:p3");
            query1.setParameter("p1", s.getStartPeriodYear());
            query1.setParameter("p2", s.getStartPeriodMonth());
            query1.setParameter("p3", s.getStartPeriodMonth() + 2);

            ArrayList<DailyMPS> dmpss = new ArrayList<DailyMPS>(query1.getResultList());

            //check whether Sop involve previous sop
            Calendar firstDay = mpsSession.getFirstDayOfMonth(s.getStartPeriodYear(), s.getStartPeriodMonth());
            if (firstDay.get(Calendar.DAY_OF_WEEK) != Calendar.MONDAY && firstDay.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY && firstDay.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
                int month, year;
                if (s.getStartPeriodMonth() == 1) {
                    month = 12;
                    year = s.getStartPeriodYear() - 1;
                } else {
                    month = s.getStartPeriodMonth() - 1;
                    year = s.getStartPeriodYear();
                }

                query = entityManager.createQuery("SELECT dmps FROM DailyMPS dmps where dmps.startPeriodYear =:p1 AND dmps.startPeriodMonth =:p2");
                query.setParameter("p1", year);
                query.setParameter("p2", month);
                if (query.getSingleResult() != null) {
                    dmpss.add((DailyMPS) query.getSingleResult());
                }
            }

            for (int i = 0; i < dmpss.size(); i++) {
                mpsSession.updateWMPSTriggerByDMPS(dmpss.get(i));
            }
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }

    @Override
    public ArrayList<DailyPlannedProduction> getMonthlyDailyPlannedProductionByProduct(Product p, int selectedYear, int selectedMonth) {
        Calendar start = Calendar.getInstance();
        start.set(selectedYear, selectedMonth - 1, 1,0,0,0);
        int max = start.getActualMaximum(Calendar.DAY_OF_MONTH);
        Calendar end = Calendar.getInstance();
        end.set(selectedYear, selectedMonth - 1, max,0,0,0);
        end.add(Calendar.DAY_OF_MONTH, 1);
        Query query = entityManager.createQuery("SELECT dpp FROM DailyPlannedProduction dpp WHERE dpp.product.id=:p1 AND dpp.productionDayL/86400000 >= :p2 AND dpp.productionDayL/86400000 <= :p3");
        query.setParameter("p1", p.getId());
        query.setParameter("p2", start.getTimeInMillis() / 86400000);
        query.setParameter("p3", end.getTimeInMillis() / 86400000);
        
        ArrayList<DailyPlannedProduction> dpps = new ArrayList<DailyPlannedProduction>(query.getResultList());
        ArrayList<DailyPlannedProduction> dpps2 = new ArrayList<DailyPlannedProduction>();
        if(dpps.isEmpty()) {
            dpps2 = null;
        }
        else {
            for (DailyPlannedProduction dpp : dpps) {
                Calendar newC = Calendar.getInstance();
                newC.setTime(dpp.getProductionDay());
                if(!isPublicHoliday(newC)) {
                    dpps2.add(dpp);
                }
            }
//            Iterator dppsIterator = dpps.iterator();
//            while(dppsIterator.hasNext()) {
//                DailyPlannedProduction dpp = (DailyPlannedProduction) dppsIterator.next();
//                Calendar newC = Calendar.getInstance();
//                newC.setTime(dpp.getProductionDay());
//                if(isPublicHoliday(newC)) {
//                    dpps.remove(dpp);
//                }
//            }
        }
        
        
        //System.out.println("###################sopSession line 507: start" +start.getTimeInMillis() / 86400000+ "End: " + end.getTimeInMillis() / 86400000 );

        return dpps2;
    }

    @Override
    public boolean checkCreatedSOPAlready(int sopStartYear, int sopStartMonth) {
        SOP sop = getSOP(sopStartYear, sopStartMonth);
        if (sop == null) {
            return false;
        } else {
            return true;
        }
    }
}
